<?php
namespace app\common\service;

use support\Log;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

/**
 * 数据导出服务
 */
class DataExportService
{
    protected $exportDir;
    protected $baseUrl;
    
    public function __construct()
    {
        $this->exportDir = runtime_path() . 'exports/';
        $this->baseUrl = config('app.app_host', 'http://localhost');
        
        // 确保导出目录存在
        if (!is_dir($this->exportDir)) {
            mkdir($this->exportDir, 0755, true);
        }
    }
    
    /**
     * 导出应用数据
     */
    public function exportAppData($params)
    {
        try {
            $appId = $params['app_id'];
            $exportType = $params['export_type'];
            $dateRange = $params['date_range'];
            $format = $params['format'];
            $advancedOptions = $params['advanced_options'] ?? [];
            $filename = $params['filename'];
            $userId = $params['user_id'];
            
            Log::info('Starting data export process', [
                'app_id' => $appId,
                'export_type' => $exportType,
                'date_range' => $dateRange,
                'format' => $format
            ]);
            
            // 获取数据
            $data = $this->getExportData($appId, $exportType, $dateRange, $advancedOptions);
            
            if (empty($data)) {
                return ['success' => false, 'message' => '没有可导出的数据'];
            }
            
            // 生成文件
            $fileResult = $this->generateExportFile($data, $format, $filename);
            
            if (!$fileResult['success']) {
                return $fileResult;
            }
            
            // 保存导出记录
            $this->saveExportRecord($userId, $appId, $exportType, $fileResult, $params);
            
            return [
                'success' => true,
                'file_path' => $fileResult['file_path'],
                'download_url' => $fileResult['download_url'],
                'file_size' => $fileResult['file_size'],
                'record_count' => count($data)
            ];
            
        } catch (\Exception $e) {
            Log::error('Data export failed', ['error' => $e->getMessage()]);
            return ['success' => false, 'message' => '数据导出失败：' . $e->getMessage()];
        }
    }
    
    /**
     * 获取导出数据
     */
    protected function getExportData($appId, $exportType, $dateRange, $advancedOptions)
    {
        $startDate = $dateRange['start'] ?? date('Y-m-d', strtotime('-7 days'));
        $endDate = $dateRange['end'] ?? date('Y-m-d');
        
        // 获取 appkey 并基于 appkey 构建查询条件
        $app = Db::table('gf_apps')
            ->where('id', $appId)
            ->where('is_del', 0)
            ->first();

        if (!$app || empty($app->appkey)) {
            return [];
        }

        $where = [
            ['appkey', '=', $app->appkey],
            ['created_at', '>=', $startDate . ' 00:00:00'],
            ['created_at', '<=', $endDate . ' 23:59:59']
        ];
        
        // 处理高级选项（事件/访问表没有 is_test 字段）
        if (!in_array('includeDeleted', $advancedOptions)) {
            $where[] = ['is_del', '=', 0];
        }
        
        $data = [];
        
        switch ($exportType) {
            case 'basic':
                $data = $this->getBasicInfo($appId);
                break;
                
            case 'statistics':
                $data = $this->getStatisticsData($appId, $startDate, $endDate, $where);
                break;
                
            case 'installs':
                $data = $this->getInstallData($appId, $startDate, $endDate, $where);
                break;
                
            case 'events':
                $data = $this->getEventData($appId, $startDate, $endDate, $where);
                break;
                
            case 'comprehensive':
                $data = $this->getComprehensiveData($appId, $startDate, $endDate, $where);
                break;
        }
        
        return $data;
    }
    
    /**
     * 获取基础信息
     */
    protected function getBasicInfo($appId)
    {
        $app = Db::table('gf_apps')
            ->where('id', $appId)
            ->where('is_del', 0)
            ->first();
            
        if (!$app) {
            return [];
        }
        
        return [[
            '应用ID' => $app->id,
            '应用名称' => $app->name,
            '应用包名' => $app->package_name ?? '',
            'APP Key' => $app->appkey,
            '应用状态' => $this->getStatusText($app->status),
            '创建时间' => $app->created_at,
            '更新时间' => $app->updated_at ?? ''
        ]];
    }
    
    /**
     * 获取统计数据
     */
    protected function getStatisticsData($appId, $startDate, $endDate, $where)
    {
        // 按小时统计
        $statistics = Db::table('gf_et_apps_apps')
            ->select(
                Db::raw('DATE(created_at) as date'),
                Db::raw('HOUR(created_at) as hour'),
                Db::raw('COUNT(*) as access_count'),
                Db::raw('COUNT(DISTINCT fingerprint) as unique_devices')
            )
            ->where($where)
            ->groupBy('date', 'hour')
            ->orderBy('date', 'asc')
            ->orderBy('hour', 'asc')
            ->get();
            
        $result = [];
        foreach ($statistics as $stat) {
            $result[] = [
                '日期' => $stat->date,
                '小时' => $stat->hour,
                '访问量' => $stat->access_count,
                '独立设备数' => $stat->unique_devices,
                '时间戳' => $stat->date . ' ' . str_pad($stat->hour, 2, '0', STR_PAD_LEFT) . ':00:00'
            ];
        }
        
        return $result;
    }
    
    /**
     * 获取安装数据
     */
    protected function getInstallData($appId, $startDate, $endDate, $where)
    {
        $installs = Db::table('gf_et_apps_event')
            ->select(
                'id',
                'appkey',
                'custom',
                'device',
                'fingerprint',
                'version',
                'created_at'
            )
            ->where($where)
            ->whereRaw("JSON_EXTRACT(custom, '$.event_type') = 'install'")
            ->orderBy('created_at', 'desc')
            ->get();
        
        $result = [];
        foreach ($installs as $install) {
            $custom = json_decode($install->custom ?? '{}', true) ?: [];
            $device = json_decode($install->device ?? '{}', true) ?: [];

            $result[] = [
                '事件ID' => $install->id,
                '设备ID' => $device['id'] ?? $device['device_id'] ?? ($install->fingerprint ?? ''),
                '事件类型' => $custom['event_type'] ?? 'install',
                '事件值' => $custom['event_value'] ?? $custom['value'] ?? '',
                'IP地址' => $device['ip'] ?? ($custom['ip'] ?? ''),
                '用户代理' => $device['user_agent'] ?? ($device['ua'] ?? ''),
                '来源页面' => $custom['referer'] ?? ($custom['referrer'] ?? ''),
                '创建时间' => $install->created_at
            ];
        }
        
        return $result;
    }
    
    /**
     * 获取事件数据
     */
    protected function getEventData($appId, $startDate, $endDate, $where)
    {
        $events = Db::table('gf_et_apps_event')
            ->select(
                'id',
                'appkey',
                'custom',
                'device',
                'fingerprint',
                'version',
                'created_at'
            )
            ->where($where)
            ->orderBy('created_at', 'desc')
            ->limit(10000)
            ->get();
        
        $result = [];
        foreach ($events as $event) {
            $custom = json_decode($event->custom ?? '{}', true) ?: [];
            $device = json_decode($event->device ?? '{}', true) ?: [];

            $result[] = [
                '事件ID' => $event->id,
                '设备ID' => $device['id'] ?? $device['device_id'] ?? ($event->fingerprint ?? ''),
                '事件类型' => $custom['event_type'] ?? '',
                '事件值' => $custom['event_value'] ?? $custom['value'] ?? '',
                'IP地址' => $device['ip'] ?? ($custom['ip'] ?? ''),
                '用户代理' => $device['user_agent'] ?? ($device['ua'] ?? ''),
                '来源页面' => $custom['referer'] ?? ($custom['referrer'] ?? ''),
                '创建时间' => $event->created_at
            ];
        }
        
        return $result;
    }
    
    /**
     * 获取综合数据
     */
    protected function getComprehensiveData($appId, $startDate, $endDate, $where)
    {
        // 获取应用基本信息
        $appInfo = $this->getBasicInfo($appId);
        
        // 获取统计数据摘要
        $statsSummary = $this->getStatisticsSummary($appId, $startDate, $endDate, $where);
        
        // 获取安装数据摘要
        $installSummary = $this->getInstallSummary($appId, $startDate, $endDate, $where);
        
        // 合并数据
        $result = [];
        
        // 应用基本信息
        if (!empty($appInfo)) {
            $result[] = ['=== 应用基本信息 ===', '', '', '', ''];
            foreach ($appInfo as $info) {
                $result[] = array_values($info);
            }
        }
        
        // 统计数据摘要
        if (!empty($statsSummary)) {
            $result[] = ['', '', '', '', ''];
            $result[] = ['=== 统计数据摘要 ===', '', '', '', ''];
            foreach ($statsSummary as $stat) {
                $result[] = array_values($stat);
            }
        }
        
        // 安装数据摘要
        if (!empty($installSummary)) {
            $result[] = ['', '', '', '', ''];
            $result[] = ['=== 安装数据摘要 ===', '', '', '', ''];
            foreach ($installSummary as $install) {
                $result[] = array_values($install);
            }
        }
        
        return $result;
    }
    
    /**
     * 获取统计数据摘要
     */
    protected function getStatisticsSummary($appId, $startDate, $endDate, $where)
    {
        $totalAccess = Db::table('gf_et_apps_apps')
            ->where($where)
            ->count();
            
        $uniqueDevices = Db::table('gf_et_apps_apps')
            ->where($where)
            ->distinct('fingerprint')
            ->count('fingerprint');
            
        $dailyStats = Db::table('gf_et_apps_apps')
            ->select(
                Db::raw('DATE(created_at) as date'),
                Db::raw('COUNT(*) as access_count'),
                Db::raw('COUNT(DISTINCT fingerprint) as unique_devices')
            )
            ->where($where)
            ->groupBy('date')
            ->orderBy('date', 'desc')
            ->limit(30)
            ->get();
            
        $result = [];
        $result[] = [
            '统计项',
            '数值',
            '说明',
            '',
            ''
        ];
        
        $result[] = [
            '总访问量',
            $totalAccess,
            $startDate . ' 至 ' . $endDate,
            '',
            ''
        ];
        
        $result[] = [
            '独立设备数',
            $uniqueDevices,
            '去重后的设备数量',
            '',
            ''
        ];
        
        if (!empty($dailyStats)) {
            foreach ($dailyStats as $stat) {
                $result[] = [
                    $stat->date,
                    $stat->access_count . ' 次访问',
                    $stat->unique_devices . ' 设备',
                    '',
                    ''
                ];
            }
        }
        
        return $result;
    }
    
    /**
     * 获取安装数据摘要
     */
    protected function getInstallSummary($appId, $startDate, $endDate, $where)
    {
        $totalInstalls = Db::table('gf_et_apps_event')
            ->where($where)
            ->whereRaw("JSON_EXTRACT(custom, '$.event_type') = 'install'")
            ->count();
            
        $dailyInstalls = Db::table('gf_et_apps_event')
            ->select(
                Db::raw('DATE(created_at) as date'),
                Db::raw('COUNT(*) as install_count')
            )
            ->where($where)
            ->whereRaw("JSON_EXTRACT(custom, '$.event_type') = 'install'")
            ->groupBy('date')
            ->orderBy('date', 'desc')
            ->limit(30)
            ->get();
            
        $result = [];
        $result[] = [
            '日期',
            '安装数',
            '说明',
            '',
            ''
        ];
        
        $result[] = [
            '总安装数',
            $totalInstalls,
            $startDate . ' 至 ' . $endDate,
            '',
            ''
        ];
        
        if (!empty($dailyInstalls)) {
            foreach ($dailyInstalls as $install) {
                $result[] = [
                    $install->date,
                    $install->install_count,
                    '安装事件',
                    '',
                    ''
                ];
            }
        }
        
        return $result;
    }
    
    /**
     * 生成导出文件
     */
    protected function generateExportFile($data, $format, $filename)
    {
        try {
            $timestamp = date('YmdHis');
            $safeFilename = preg_replace('/[^a-zA-Z0-9_-]/', '_', $filename);
            $fileName = $timestamp . '_' . $safeFilename;
            $filePath = $this->exportDir . $fileName;
            
            switch ($format) {
                case 'excel':
                    $filePath .= '.xlsx';
                    $result = $this->generateExcelFile($data, $filePath);
                    break;
                    
                case 'csv':
                    $filePath .= '.csv';
                    $result = $this->generateCsvFile($data, $filePath);
                    break;
                    
                case 'json':
                    $filePath .= '.json';
                    $result = $this->generateJsonFile($data, $filePath);
                    break;
                    
                default:
                    return ['success' => false, 'message' => '不支持的文件格式'];
            }
            
            if (!$result['success']) {
                return $result;
            }
            
            $fileSize = filesize($filePath);
            $downloadUrl = $this->baseUrl . '/exports/' . basename($filePath);
            
            return [
                'success' => true,
                'file_path' => $filePath,
                'download_url' => $downloadUrl,
                'file_size' => $this->formatFileSize($fileSize)
            ];
            
        } catch (\Exception $e) {
            Log::error('Failed to generate export file', ['error' => $e->getMessage()]);
            return ['success' => false, 'message' => '文件生成失败：' . $e->getMessage()];
        }
    }
    
    /**
     * 生成Excel文件
     */
    protected function generateExcelFile($data, $filePath)
    {
        try {
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            
            // 写入数据
            $row = 1;
            foreach ($data as $record) {
                $col = 1;
                foreach ($record as $value) {
                    $sheet->setCellValueByColumnAndRow($col, $row, $value);
                    $col++;
                }
                $row++;
            }
            
            // 自动调整列宽
            foreach (range('A', $sheet->getHighestColumn()) as $column) {
                $sheet->getColumnDimension($column)->setAutoSize(true);
            }
            
            $writer = new Xlsx($spreadsheet);
            $writer->save($filePath);
            
            return ['success' => true];
            
        } catch (\Exception $e) {
            return ['success' => false, 'message' => 'Excel文件生成失败：' . $e->getMessage()];
        }
    }
    
    /**
     * 生成CSV文件
     */
    protected function generateCsvFile($data, $filePath)
    {
        try {
            $handle = fopen($filePath, 'w');
            
            foreach ($data as $record) {
                // 处理中文字符编码
                $encodedRecord = array_map(function($value) {
                    return mb_convert_encoding($value ?? '', 'UTF-8', 'UTF-8');
                }, $record);
                
                fputcsv($handle, $encodedRecord);
            }
            
            fclose($handle);
            
            return ['success' => true];
            
        } catch (\Exception $e) {
            return ['success' => false, 'message' => 'CSV文件生成失败：' . $e->getMessage()];
        }
    }
    
    /**
     * 生成JSON文件
     */
    protected function generateJsonFile($data, $filePath)
    {
        try {
            $jsonData = json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
            file_put_contents($filePath, $jsonData);
            
            return ['success' => true];
            
        } catch (\Exception $e) {
            return ['success' => false, 'message' => 'JSON文件生成失败：' . $e->getMessage()];
        }
    }
    
    /**
     * 保存导出记录
     */
    protected function saveExportRecord($userId, $appId, $exportType, $fileResult, $params)
    {
        try {
            $record = [
                'user_id' => $userId,
                'app_id' => $appId,
                'export_type' => $exportType,
                'file_path' => $fileResult['file_path'],
                'file_size' => $fileResult['file_size'],
                'record_count' => $params['record_count'] ?? 0,
                'date_range_start' => $params['date_range']['start'] ?? null,
                'date_range_end' => $params['date_range']['end'] ?? null,
                'format' => $params['format'],
                'advanced_options' => json_encode($params['advanced_options'] ?? []),
                'created_at' => date('Y-m-d H:i:s'),
                'status' => 'completed'
            ];
            
            Db::table('gf_data_exports')->insert($record);
            
        } catch (\Exception $e) {
            Log::error('Failed to save export record', ['error' => $e->getMessage()]);
        }
    }
    
    /**
     * 获取导出历史
     */
    public function getExportHistory($userId, $page = 1, $limit = 20)
    {
        $offset = ($page - 1) * $limit;
        
        $total = Db::table('gf_data_exports')
            ->where('user_id', $userId)
            ->count();
            
        $records = Db::table('gf_data_exports')
            ->where('user_id', $userId)
            ->orderBy('created_at', 'desc')
            ->limit($limit)
            ->offset($offset)
            ->get();
            
        return [
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'records' => $records
        ];
    }
    
    /**
     * 删除导出文件
     */
    public function deleteExportFile($userId, $fileId)
    {
        try {
            $record = Db::table('gf_data_exports')
                ->where('id', $fileId)
                ->where('user_id', $userId)
                ->first();
                
            if (!$record) {
                return ['success' => false, 'message' => '文件不存在或无权限删除'];
            }
            
            // 删除物理文件
            if (file_exists($record->file_path)) {
                unlink($record->file_path);
            }
            
            // 删除数据库记录
            Db::table('gf_data_exports')
                ->where('id', $fileId)
                ->where('user_id', $userId)
                ->delete();
                
            return ['success' => true, 'message' => '文件删除成功'];
            
        } catch (\Exception $e) {
            Log::error('Failed to delete export file', ['error' => $e->getMessage()]);
            return ['success' => false, 'message' => '文件删除失败：' . $e->getMessage()];
        }
    }
    
    /**
     * 获取状态文本
     */
    protected function getStatusText($status)
    {
        $statusMap = [
            'active' => '活跃',
            'paused' => '暂停',
            'expired' => '过期'
        ];
        
        return $statusMap[$status] ?? '未知';
    }
    
    /**
     * 格式化文件大小
     */
    protected function formatFileSize($bytes)
    {
        if ($bytes == 0) return '0 Bytes';
        
        $k = 1024;
        $sizes = ['Bytes', 'KB', 'MB', 'GB'];
        $i = floor(log($bytes) / log($k));
        
        return round($bytes / pow($k, $i), 2) . ' ' . $sizes[$i];
    }
}